package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.StateWeek;

@Repository("stateWeekDao")
public class StateWeekDaoImpl extends TemplateDaoImpl<StateWeek, Integer>implements StateWeekDao{
	/**
	 * 根据设备id，所属年份，周和设备状态查询设备状态周记录
	 * @param equId
	 * @param belongYear
	 * @param belongWeek
	 * @param stateType
	 * @return
	 */
	@Override
	public StateWeek getStateWeekByStateAndDate(int equId, int belongYear,int belongWeek,String stateType) {
		String  sql="select * from stateWeek where equId=:equId and belongYear=:belongYear and belongWeek=:belongWeek and stateType=:stateType";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("belongYear", belongYear);
		params.put("belongWeek", belongWeek);
		params.put("stateType", stateType);
		return jt.queryForObject(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateWeek.class));
	}

	/**
	 * 根据设备id，所属年份，开始周，结束周，设备状态，查询区间内的记录
	 * @param equId
	 * @param stateType
	 * @param belongYear
	 * @param startWeek
	 * @param endWeek
	 * @return
	 */
	@Override
	public List<StateWeek> getStateWeekByStateAndDates(int equId,
			String stateType, int belongYear, int startWeek, int endWeek) {
		String  sql="select * from stateWeek where equId=:equId and belongYear=:belongYear  and stateType=:stateType  and belongWeek>=:startWeek and belongWeek<=:endWeek";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("belongYear", belongYear);
		params.put("startWeek", startWeek);
		params.put("endWeek", endWeek);
		params.put("stateType", stateType);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateWeek.class));
	}

	/**
	 * 获取指定年份下，从开始周到结束周的总工作时长
	 * ps:总工作时长表示非停机的所有持续状态之和
	 * @param equId
	 * @param belongYear
	 * @param startWeek
	 * @param endWeek
	 * @return
	 */
	@Override
	public StateWeek getdurationByStateAndDate(int equId,int belongYear,int startWeek,int endWeek,String stateType) {
		StringBuilder sql = new StringBuilder("select COALESCE(SUM(duration),0) duration ,COALESCE(SUM(relativeComp),0) relativeComp from stateWeek where equId=:equId and belongYear=:belongYear and belongWeek>=:startWeek and belongWeek<=:endWeek ");
		Map<String, Object> params=new HashMap<String, Object>();
		if(stateType==null||stateType.equals("")){
			sql.append(" and stateType<>'停机' ");
		}else{
			sql.append(" and stateType=:stateType ");
			params.put("stateType", stateType);
		}
		params.put("equId", equId);
		params.put("belongYear", belongYear);
		params.put("startWeek", startWeek);
		params.put("endWeek", endWeek);
		return jt.queryForObject(sql.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(StateWeek.class));
		}

	@Override
	public List<StateWeek> getDistinctType(int equId, int belongYear,
			int startWeek, int endWeek) {
		String  sql="select distinct(stateType) from stateWeek where equId=:equId and belongYear=:belongYear   and belongWeek>=:startWeek and belongWeek<=:endWeek";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("belongYear", belongYear);
		params.put("startWeek", startWeek);
		params.put("endWeek", endWeek);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateWeek.class));
	
	}

}
